﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;

namespace DAO
{
    public class SACHDAO
    {
        public bool themsach(SACHDTO sDTO)
        {
            SqlConnection conn = DATAPROVIDER.ConnectSQL();
            string sql = "INSERT INTO SACH VALUES(@MS,@TS,@MTG,@MTL,@NXB,@MNXB,@LTB,@NN,@SL,@DG,@MNV,@SQ,@NG)";
            SqlCommand cmd = new SqlCommand(sql, conn);

            SqlParameter para = new SqlParameter("@MS", System.Data.SqlDbType.NChar, 10);
            para.Value = sDTO.Masach;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@TS", System.Data.SqlDbType.NVarChar, 50);
            para.Value = sDTO.Tensach;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@MTG", System.Data.SqlDbType.Int);
            para.Value = sDTO.Matacgia;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@MTL", System.Data.SqlDbType.NChar, 3);
            para.Value=sDTO.Maloaisach;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@NXB",System.Data.SqlDbType.Int);
            para.Value = sDTO.Namxuatban;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@MNXB",System.Data.SqlDbType.Int);
            para.Value = sDTO.Manhaxuatban;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@LTB",System.Data.SqlDbType.Int);
            para.Value = sDTO.Lantaiban;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@NN",System.Data.SqlDbType.DateTime);
            para.Value = sDTO.Ngaynhap;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@SL",System.Data.SqlDbType.Int);
            para.Value = sDTO.Soluong;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@DG",System.Data.SqlDbType.Float);
            para.Value = sDTO.Dongia;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@MNV",System.Data.SqlDbType.NChar,6);
            para.Value = sDTO.Manhanvien;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@SQ",System.Data.SqlDbType.NChar,2);
            para.Value = sDTO.Sachquy;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@NG",System.Data.SqlDbType.Int);
            para.Value = sDTO.Ngonngu;
            cmd.Parameters.Add(para);
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            conn.Dispose();
            if (kq > 0)
            {
                BIENTHELOAISACHDAO btlsDAO = new BIENTHELOAISACHDAO();
                btlsDAO.updatebientheloaisach(sDTO.Maloaisach);
                return true;
            }
            return false;
        }

        public int update(string strquery, DataSet dataset)
        {
            int effrow = 0;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = strquery;
            SqlConnection conn = DATAPROVIDER.ConnectSQL();
            cmd.Connection = conn;
            SqlDataAdapter sqldataadapter = new SqlDataAdapter(cmd);
            SqlCommandBuilder sqlcomandbuilder = new SqlCommandBuilder(sqldataadapter);
            effrow = sqldataadapter.Update(dataset.Tables["sach"]);
            sqldataadapter.Dispose();
            conn.Close();
            conn.Dispose();
            return effrow;
        }
        public DataTable Bangsach()
        {
            DataTable datatable = new DataTable();
            SqlConnection conn = DATAPROVIDER.ConnectSQL();
            string sql = "SELECT * FROM SACH";
            SqlDataAdapter sqldataadapter = new SqlDataAdapter(sql, conn);
            sqldataadapter.Fill(datatable);
            conn.Close();
            conn.Dispose();
            sqldataadapter.Dispose();
            return datatable;
        }
        public string masach(string matheloai,string tensach,string matacgia,string quyenso,bool co)
        {
            //sach cu
            string masachtemp = "";
            SqlConnection conn = DATAPROVIDER.ConnectSQL();
            if (co == false)
            {
                string sql = "SELECT MIN(MaSach) FROM SACH WHERE MaTheLoai='" + matheloai + "' AND TenSach = N'" + tensach + "' AND MaTacGia = '" + matacgia + "'";
                SqlCommand cmd = new SqlCommand(sql, conn);
                object temp = cmd.ExecuteScalar();
                string masachcsdl = temp.ToString();
                string masachout = masachcsdl.Substring(2, 3);//LẤY SỐ THỨ TỰ SÁCH TRONG KHO SÁCH
                string thutu = masachcsdl.Substring(6, 2);//LẤY SỐ THỨ TỰ QUYỂN SÁCH TRONG BỘ SÁCH
                string temp1 = "0";
                int thu = Convert.ToInt32(thutu);
                if (thu == Convert.ToInt32(quyenso))
                    return "-1";
                if (quyenso.Length != 2)
                    quyenso = temp1 + quyenso;
                masachtemp = matheloai.Trim() + masachout + "_" + quyenso;
            }
            else//sách mới
            {
                string sql = "SELECT GiaTri FROM BIEN_THE_LOAI_SACH WHERE TheLoai = '" + matheloai + "'";
                SqlCommand cmd = new SqlCommand(sql, conn);
                object temp = cmd.ExecuteScalar();
                int thutu = Convert.ToInt32(temp);
                thutu++;
                string thu = thutu.ToString();
                string temp1 = "0";
                while (thu.Length != 3)
                    thu = temp1 + thu;
                if (quyenso.Length != 2)
                    quyenso = temp1 + quyenso;
                masachtemp = matheloai.Trim() + thu.Trim() + "_" + quyenso.Trim(); 
            }
            conn.Close();
            conn.Dispose();
            return masachtemp;
        }
    }
}
